package com.ksv.lop;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class ConnectDB extends SQLiteOpenHelper {

	static final String dbName = "qlsvDB";
	
	static public final String masv = "MaSV";
	static public final String hosv = "HoSV";
	static public final String tensv = "TenSV";
	static public final String hoten = "HoTen";
	static public final String gioitinh = "GioiTinh";
	static public final String diachi = "DiaChi";
	
	static public final String makhoa = "MaKhoa";
	static public final String tenkhoa = "TenKhoa";
	
	static public final String malop = "MaLop";
	static public final String tenlop = "TenLop";
	
	static public final String mamon = "MaMon";
	static public final String tenmon = "TenMon";
	
	static public final String mahk = "MaHK";
	static public final String tenhk = "TenHK";
	
	static public final String diem = "Diem";
	
	static public MonHoc mon_hoc = null;
	
	static private SQLiteDatabase dbWriter = null;
	static private SQLiteDatabase dbReader = null;
	
	
	public ConnectDB(Context context) {
		super(context, dbName, null, 2);
		dbReader = this.getReadableDatabase();
		dbWriter = this.getWritableDatabase();
	}
								
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		try{
			Log.i("TTTH", "CREATE TABLE BEGIN");
			
			db.execSQL("CREATE TABLE KHOA (MaKhoa INTEGER NOT NULL PRIMARY KEY, TenKhoa TEXT)");
			
			Log.i("TTTH", "CREATE TABLE KHOA OK");
			
			db.execSQL("CREATE TABLE LOP (MaLop INTEGER NOT NULL PRIMARY KEY, TenLop TEXT, MaKhoa INTEGER  NOT NULL, FOREIGN KEY (MaKhoa) REFERENCES KHOA(MaKhoa))");
			
			Log.i("TTTH", "CREATE TABLE LOP OK");
			
			db.execSQL("CREATE TABLE SINHVIEN (MaSV INTEGER NOT NULL PRIMARY KEY, HoSV TEXT NOT NULL, TenSV TEXT NOT NULL, GioiTinh BOOLEAN, DiaChi TEXT, MaLop INTEGER  NOT NULL, FOREIGN KEY (MaLop) REFERENCES LOP(MaLop))");
			
			Log.i("TTTH", "CREATE TABLE SINHVIEN OK");
			
			db.execSQL("CREATE TABLE MONHOC (MaMon INTEGER  NOT NULL PRIMARY KEY, TenMon TEXT  NOT NULL)");
			
			Log.i("TTTH", "CREATE TABLE MONHOC OK");
			
			db.execSQL("CREATE TABLE HOCKY (MaHK INTEGER  NOT NULL PRIMARY KEY, TenHK TEXT NOT NULL)");
			
			Log.i("TTTH", "CREATE TABLE HOCKY OK");
			
			db.execSQL("CREATE TABLE KETQUA (MaSV INTEGER NOT NULL, MaMon INTEGER NOT NULL, MaHK INTEGER NOT NULL, Diem INTEGER NOT NULL, CONSTRAINT pk_ketqua_masv_mon_hk PRIMARY KEY(MaSV,MaMon,MaHK))");
			
			Log.i("TTTH", "CREATE TABLE KETQUA OK");
			
			InsertKhoa(db);
			
			Log.i("TTTH", "INSERT TABLE KHOA OK");
			
			InsertLop(db);
			
			Log.i("TTTH", "INSERT TABLE LOP OK");
			
			InsertMonHoc(db);
			
			Log.i("TTTH", "INSERT TABLE MONHOC OK");
			
			InsertHocKy(db);
			
			Log.i("TTTH", "INSERT TABLE HOCKY OK");
		}
		catch(Exception ex){
			
		}
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		
	}
	
	@Override
	public void onOpen(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		super.onOpen(db);
	}

	@Override
	public synchronized void close() {
		// TODO Auto-generated method stub
		super.close();
	}

	@Override
	public synchronized SQLiteDatabase getReadableDatabase() {
		// TODO Auto-generated method stub
		return super.getReadableDatabase();
	}

	@Override
	public synchronized SQLiteDatabase getWritableDatabase() {
		// TODO Auto-generated method stub
		return super.getWritableDatabase();
	}

	public void InsertKhoa(SQLiteDatabase db){
		ContentValues cv = new ContentValues();
		cv.put(makhoa, 1);
		cv.put(tenkhoa, "KHOA CONG NGHE THONG TIN");
		db.insert("KHOA", makhoa, cv);		
		cv.put(makhoa, 2);
		cv.put(tenkhoa, "KHOA CO DIEN TU");
		db.insert("KHOA", makhoa, cv);
		cv.put(makhoa, 3);
		cv.put(tenkhoa, "KHOA NGOAI NGU");
		db.insert("KHOA", makhoa, cv);
		cv.put(makhoa, 4);
		cv.put(tenkhoa, "KHOA CO KHI MAY");
		db.insert("KHOA", makhoa, cv);
		cv.put(makhoa, 5);
		cv.put(tenkhoa, "KHOA CONG NGHE MOI TRUONG");
		db.insert("KHOA", makhoa, cv);
	}
	
	private void InsertLop(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		ContentValues cv = new ContentValues();
		cv.put(malop, 1);
		cv.put(tenlop, "051101");
		cv.put(makhoa, 1);
		db.insert("LOP", malop, cv);		
		cv.put(malop, 2);
		cv.put(tenlop, "051102");
		cv.put(makhoa, 1);
		db.insert("LOP", malop, cv);	
		cv.put(malop, 3);
		cv.put(tenlop, "051201");
		cv.put(makhoa, 2);
		db.insert("LOP", malop, cv);	
		cv.put(malop, 4);
		cv.put(tenlop, "051202");
		cv.put(makhoa, 2);
		db.insert("LOP", malop, cv);	
	}

	public void InsertMonHoc(SQLiteDatabase db){
		ContentValues cv = new ContentValues();
		cv.put(mamon, 1);
		cv.put(tenmon, "TOAN A1");	
		db.insert("MONHOC", mamon, cv);
		cv.put(mamon, 2);
		cv.put(tenmon, "TOAN A2");	
		db.insert("MONHOC", mamon, cv);
		cv.put(mamon, 3);
		cv.put(tenmon, "TOAN A3");	
		db.insert("MONHOC", mamon, cv);
		cv.put(mamon, 4);
		cv.put(tenmon, "LAP TRINH WEB");	
		db.insert("MONHOC", mamon, cv);
		cv.put(mamon, 5);
		cv.put(tenmon, "LAP TRINH JAVA");	
		db.insert("MONHOC", mamon, cv);
	}
	
	public void InsertHocKy(SQLiteDatabase db){
		ContentValues cv = new ContentValues();
		cv.put(mahk, 1);
		cv.put(tenhk, "HOC KY 1");	
		db.insert("HOCKY", mahk, cv);	
		cv.put(mahk, 2);
		cv.put(tenhk, "HOC KY 2");	
		db.insert("HOCKY", mahk, cv);
		cv.put(mahk, 3);
		cv.put(tenhk, "HOC KY 3");	
		db.insert("HOCKY", mahk, cv);
		cv.put(mahk, 4);
		cv.put(tenhk, "HOC KY 4");	
		db.insert("HOCKY", mahk, cv);
		cv.put(mahk, 5);
		cv.put(tenhk, "HOC KY 5");	
		db.insert("HOCKY", mahk, cv);
		cv.put(mahk, 6);
		cv.put(tenhk, "HOC KY 6");	
		db.insert("HOCKY", mahk, cv);
		cv.put(mahk, 7);
		cv.put(tenhk, "HOC KY 7");	
		db.insert("HOCKY", mahk, cv);
		cv.put(mahk, 8);
		cv.put(tenhk, "HOC KY 8");	
		db.insert("HOCKY", mahk, cv);
		cv.put(mahk, 9);
		cv.put(tenhk, "HOC KY 9");	
		db.insert("HOCKY", mahk, cv);
	 }
	
	 public static Cursor getAllSinhVien(){
		 Cursor cur = dbReader.rawQuery("SELECT MASV AS _id,* FROM SINHVIEN ORDER BY TENSV ASC", new String[]{});
		 return cur;
	 }
	 
	 public static Cursor getAllSinhVien_Search(String search){
		 Cursor cur = dbReader.rawQuery("SELECT MASV AS _id,* FROM SINHVIEN WHERE TENSV LIKE '"+ search +"%'", new String[]{});
		 return cur;
	 }
	 
	 public static Cursor getAllKhoa(){
		 Cursor cur = dbReader.rawQuery("SELECT MAKHOA AS _id,* FROM KHOA ORDER BY TENKHOA ASC", new String[]{});
		 return cur;
	 }
	 
	 public static Cursor getAllLopHoc(){
		 Cursor cur = dbReader.rawQuery("SELECT MALOP AS _id,* FROM LOP ORDER BY TENLOP ASC", new String[]{});
		 return cur;	
	 }
	 
	 public static String getTenLop(int malop){
		 Cursor cur = dbReader.rawQuery("SELECT TENLOP FROM LOP WHERE MALOP=?", new String[]{String.valueOf(malop)});
		 cur.moveToFirst();
		 return cur.getString(0);	
	 }
	 
	 public static Cursor getAllMonHoc(){
		 Cursor cur = dbReader.rawQuery("SELECT MAMON AS _id,* FROM MONHOC ORDER BY TENMON ASC", new String[]{});
		 return cur;	
	 }
	 
	 public static Cursor getAllMonHoc(String mamon){
		 Cursor cur = dbReader.rawQuery("SELECT MAMON AS _id,* FROM MONHOC WHERE MAMON=?", new String[]{mamon});
		 return cur;	
	 }
	 
	 public static Cursor getAllMonHoc_Search(String search){
		 Cursor cur = dbReader.rawQuery("SELECT MAMON AS _id,* FROM MONHOC WHERE TENMON LIKE '"+ search +"%'", null);
		 return cur;	
	 }
	 
	 public static Cursor getAllHocKy_Search(String search){
		 Cursor cur = dbReader.rawQuery("SELECT MAHK AS _id,* FROM HOCKY WHERE TENHK LIKE '"+ search +"%'", null);
		 return cur;	
	 }
	 
	 public static Cursor getAllLopHocSearch(String search)
	 {
		 Cursor cur = dbReader.rawQuery("SELECT MALOP AS _id,* FROM LOP WHERE TENLOP LIKE '"+ search +"%'", null);
		 return cur;	
	 }
	 
	 public static Cursor getAllLopHocKhoa(int makhoa)
	 {
		 Cursor cur = dbReader.rawQuery("SELECT MALOP AS _id,* FROM LOP WHERE MAKHOA=?", new String[]{String.valueOf(makhoa)});
		 return cur;	
	 }
	 
	 public static Cursor getAllHocKy(){
		 Cursor cur = dbReader.rawQuery("SELECT MAHK AS _id,* FROM HOCKY ORDER BY TENHK ASC", new String[]{});
		 return cur;
	 }
	 
	 public static Cursor getAllKetQua(){
		 Cursor cur = dbReader.rawQuery("SELECT MAMON AS _id, * FROM MONHOC", new String[]{});
		 return cur;
	 }
	 
	 public static Cursor getAllKetQua2(){
		 Cursor cur = dbReader.rawQuery("SELECT mh.TENMON, kq.* FROM KETQUA kq INNER JOIN MONHOC mh ON kq.MAMON=mh.MAMON", new String[]{});
		 return cur;
	 }
	 
	 public static String getTenMon(int mamon){		
		 Cursor c = dbReader.rawQuery("SELECT TENMON FROM MONHOC WHERE MAMON=?", new String[]{String.valueOf(mamon)});
		 c.moveToFirst();
		 return c.getString(0);
	 }
	 
	 public static String getTenHocKy(int mahk){		
		 Cursor c = dbReader.rawQuery("SELECT TENHK FROM HOCKY WHERE MAHK=?", new String[]{String.valueOf(mahk)});
		 c.moveToFirst();
		 if(c.getCount() == 0) 
			 return "";
		 return c.getString(0);
	 }
	 
	 public static int getMonHocMax(){
		 Cursor c = dbReader.rawQuery("SELECT MAMON FROM MONHOC ORDER BY MAMON DESC", new String[]{});
		 c.moveToFirst();
		 if(c.getCount() == 0) 
			 return 1;
		 return c.getInt(0) + 1;
	 }
	 
	 public static int getSinhVienMax(){
		 Cursor c = dbReader.rawQuery("SELECT MASV FROM SINHVIEN ORDER BY MASV DESC", new String[]{});
		 c.moveToFirst();
		 if(c.getCount() == 0) 
			 return 1;
		 return c.getInt(0) + 1;
	 }
	 
	 public static int getHocKyMax(){
		 Cursor c = dbReader.rawQuery("SELECT MAHK FROM HOCKY ORDER BY MAHK DESC", new String[]{});
		 c.moveToFirst();
		 if(c.getCount() == 0) 
			 return 1;
		 return c.getInt(0) + 1;
	 }
	 
	 public static int getLopHocMax(){
		 Cursor c = dbReader.rawQuery("SELECT MALOP FROM LOP ORDER BY MALOP DESC", new String[]{});
		 c.moveToFirst();
		 if(c.getCount() == 0) 
			 return 1;
		 return c.getInt(0) + 1;
	 }
	 
	 public static int getLopHocMaKhoa(int malop){
		 Cursor c = dbReader.rawQuery("SELECT MAKHOA FROM LOP WHERE MALOP=?", new String[]{String.valueOf(malop)});
		 c.moveToFirst();
		 return c.getInt(0);
	 }
	 
	 public static void insertKhoa(Khoa kh){
		ContentValues cv = new ContentValues();
		cv.put(makhoa, kh.getMaKhoa());
		cv.put(tenkhoa, kh.getTenKhoa());
		dbWriter.insert("KHOA", tenkhoa, cv);
	}
	 
	 public static int updateKhoa(Khoa kh)
	 {
		 int kq = -1;
		 ContentValues cv = new ContentValues();
		 cv.put(tenkhoa, kh.getTenKhoa());
		 kq = dbWriter.update("KHOA", cv, makhoa + "=?", new String []{String.valueOf(kh.getMaKhoa())});
		 return kq;
	 }
	 
	 public static long AddMonHoc(MonHoc mh)
	 {
		ContentValues cv = new ContentValues();
		cv.put(mamon, mh.getMaMon());
		cv.put(tenmon, mh.getTenMon());	
		long kq = dbWriter.insert("MONHOC", mamon, cv);
		return kq;
	 }
	 
	 public static long AddSinhVien(SinhVien sv){
		ContentValues cv = new ContentValues();
		cv.put(masv, sv.getMaSV());
		cv.put(hosv, sv.getHoSV());	
		cv.put(tensv, sv.getTenSV());
		cv.put(gioitinh, sv.getGioiTinh());	
		cv.put(diachi, sv.getDiaChi());
		cv.put(malop, sv.getMaLop());	
		long kq = dbWriter.insert("SINHVIEN", masv, cv);
		return kq;
	 }
	 
	 public static long AddKetQua(KetQua ketqua){
			ContentValues cv = new ContentValues();
			cv.put(masv, ketqua.getMaSV());
			cv.put(mahk, ketqua.getMaHK());	
			cv.put(mamon, ketqua.getMaMon());
			cv.put(diem, ketqua.getDiem());	
			long kq = dbWriter.insert("KETQUA", masv, cv);
			return kq;
		 }
	 
	 public static long AddHocKy(HocKy hk)
	 {
		ContentValues cv = new ContentValues();
		cv.put(mahk, hk.getMaHocKy());
		cv.put(tenhk, hk.getTenHocKy());	
		long kq = dbWriter.insert("HOCKY", mahk, cv);
		return kq;
	 }
	 
	 public static long AddLopHoc(LopHoc lop)
	 {
		ContentValues cv = new ContentValues();
		cv.put(malop, lop.getMaLop());
		cv.put(tenlop, lop.getTenLop());	
		cv.put(makhoa, lop.getMaKhoa());	
		long kq = dbWriter.insert("LOP", malop, cv);
		return kq;
	 }
	 
	 public static int updateMonHoc(MonHoc mh){
		 int kq = -1;
		 ContentValues cv = new ContentValues();
		 cv.put(tenmon, mh.getTenMon());
		 kq = dbWriter.update("MONHOC", cv, mamon + "=?", new String []{String.valueOf(mh.getMaMon())});
		 return kq;
	 }
	 
	 public static int updateSinhVien(SinhVien sv){
		 int kq = -1;
		 ContentValues cv = new ContentValues();
		 cv.put(hosv, sv.getHoSV());
		 cv.put(tensv, sv.getTenSV());
		 cv.put(gioitinh, sv.getGioiTinh());
		 cv.put(diachi, sv.getDiaChi());
		 cv.put(malop, sv.getMaLop());
		 kq = dbWriter.update("SINHVIEN", cv, masv + "=?", new String []{String.valueOf(sv.getMaSV())});
		 return kq;
	 }
	 
	 public static int updateHocKy(HocKy hk)
	 {
		 int kq = -1;
		 ContentValues cv = new ContentValues();
		 cv.put(tenhk, hk.getTenHocKy());
		 kq = dbWriter.update("HOCKY", cv, mahk + "=?", new String []{String.valueOf(hk.getMaHocKy())});
		 dbWriter.close();
		 return kq;
	 }
	 
	 public static int updateLopHoc(LopHoc lop)
	 {
		 int kq = -1;
		 ContentValues cv = new ContentValues();
		 cv.put(tenlop, lop.getTenLop());
		 cv.put(makhoa, lop.getMaKhoa());
		 kq = dbWriter.update("LOP", cv, malop + "=?", new String []{String.valueOf(lop.getMaLop())});
		 return kq;
	 }
	 
	 public static void deleteKhoa(Khoa kh)
	 {
		 dbWriter.delete("KHOA", makhoa + "=?", new String []{String.valueOf(kh.getMaKhoa())});
	 }
	 
	 public static void deleteMonHoc(MonHoc mh){
		 dbWriter.delete("MONHOC", mamon + "=?", new String []{String.valueOf(mh.getMaMon())});
	 }
	 
	 public static void deleteSinhVien(SinhVien sv){
		 dbWriter.delete("SINHVIEN", masv + "=?", new String []{String.valueOf(sv.getMaSV())});
	 }
	 
	 public static void deleteHocKy(HocKy hk)
	 {
		 dbWriter.delete("HOCKY", mahk + "=?", new String []{String.valueOf(hk.getMaHocKy())});
	 }
	 
	 public static void deleteLopHoc(LopHoc lop)
	 {
		 dbWriter.delete("LOP", malop + "=?", new String []{String.valueOf(lop.getMaLop())});
	 }
	 
	 public static void deleteMonHoc(){
		 dbWriter.delete("MONHOC", "1=1", new String []{});		 
	 }
	 
	 public static void deleteSinhVien(){
		 dbWriter.delete("SINHVIEN", "1=1", new String []{});		 
	 }
	 
	 public static void deleteLopHoc()
	 {
		 dbWriter.delete("LOP", "1=1", new String []{});		 
	 }
	 
	 public static void deleteHocKy()
	 {
		 dbWriter.delete("HOCKY", "1=1", new String []{});		 
	 }
	 
}
